import com.eltima.components.ui.DatePicker;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;

import javax.swing.*;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.List;

public class JavaGui extends JFrame
        implements ActionListener {
    private static final long serialVersionUID = 1L;
    private JButton btnOk;
    private JButton chooseJButton;
    private JLabel lName;
    private JLabel lPw;
    private JLabel lPid;
    private DateSelector startDp;
    private DateSelector endDp;
    private TextField tPid, tpDir;
    private HashMap<String, Integer> maps = new HashMap<String, Integer>();
    private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private JFileChooser jfc = new JFileChooser(new File("."));
    private JButton bOpen;

    //初始化界面
    public JavaGui() {
        Container c = this.getContentPane();
        c.setLayout(new FlowLayout(FlowLayout.CENTER, 10, 8));

        lName = new JLabel("开始日期：");
        lPw = new JLabel("结束日期：");
        lPid = new JLabel("  定单编号：");
        startDp = new DateSelector();
        final DateSelector.DateChooser startDateChooser = startDp.getInstance();
        startDp.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {

                Point p = getLocationOnScreen();

                p.y = p.y + 30;

                String tmp = startDp.getText();
                startDateChooser.showDateChooser(p);
                System.out.println(startDp.getText());
                try {
                    Date startDate = simpleDateFormat.parse(startDp.getText());
                    Date endDate = simpleDateFormat.parse(endDp.getText());
                    int compareTo = startDate.compareTo(endDate);
                    if (compareTo > 0) {
                        startDp.setText(tmp);
                        JOptionPane.showMessageDialog(startDp, "开始日期不能大于结束日期", "标题", JOptionPane.WARNING_MESSAGE);
                    }
                } catch (ParseException parseException) {
                    parseException.printStackTrace();
                }
            }
        });

        endDp = new DateSelector();
        final DateSelector.DateChooser endDateChooser = endDp.getInstance();
        endDp.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {

                Point p = getLocationOnScreen();

                p.y = p.y + 30;

                String tmp = endDp.getText();
                endDateChooser.showDateChooser(p);
                System.out.println(endDp.getText());
                try {
                    Date startDate = simpleDateFormat.parse(startDp.getText());
                    Date endDate = simpleDateFormat.parse(endDp.getText());
                    int compareTo = startDate.compareTo(endDate);
                    if (compareTo > 0) {
                        endDp.setText(tmp);
                        JOptionPane.showMessageDialog(endDp, "开始日期不能大于结束日期", "标题", JOptionPane.WARNING_MESSAGE);
                    }
                } catch (ParseException parseException) {
                    parseException.printStackTrace();
                }
            }
        });
        tPid = new TextField(14);
        tpDir = new TextField(26);
        tpDir.setEditable(false);
        btnOk = new JButton("导 出");
        chooseJButton = new DateSelector();
        bOpen = new JButton("选择文件");

        //c.add(tpDir);
        //c.add(bOpen);
        //c.add(chooseJButton);
        c.add(lName);
        c.add(startDp);
        c.add(lPw);
        c.add(endDp);
        c.add(lPid);
        c.add(tPid);
        c.add(btnOk);

        // 2个按钮注册监听器
        btnOk.addActionListener(this);
        bOpen.addActionListener(this);



        this.setTitle("报表导出");
        this.setBounds(400, 300, 262, 180);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setVisible(true);
        System.out.println(System.getProperty("user.dir"));
        //判断文件是否存在
        findFile(".mdb", new File(System.getProperty("user.dir")));
        startDp.setText(simpleDateFormat.format(new Date()));
        endDp.setText(simpleDateFormat.format(new Date()));
    }

    private void findFile(String filename, File dir) {
        File[] files = dir.listFiles();
        if (files == null || files.length == 0) {
            JOptionPane.showMessageDialog(this, "在该目录下没有找到文件", "标题", JOptionPane.WARNING_MESSAGE);
            return;
        }
        //判断是否有找到文件
        boolean flag = false;
        for (File file : files) {
            if (file.isFile() && file.getName().contains(".mdb")) {
                flag = true;
                tpDir.setText(file.getAbsolutePath());
                System.out.println(file.getAbsolutePath());
                break;
            }
        }
        if (!flag) {
            JOptionPane.showMessageDialog(this, "在该目录下没有找到文件", "标题", JOptionPane.WARNING_MESSAGE);
            return;
        }
    }

    //按钮事件处理
    public void actionPerformed(ActionEvent e) {
        //登陆按钮
        if (e.getSource() == bOpen) {
            //打开文件选择器对话框
            int status = jfc.showOpenDialog(this);
            //没有选打开按钮结果提示
            if (status != JFileChooser.APPROVE_OPTION) {
                tpDir.setText("没有选中文件");
            } else {
                //被选中的文件保存为文件对象
                File file = jfc.getSelectedFile();
                tpDir.setText(file.getPath());
            }
        }
        if (e.getSource() == btnOk) {

            if ("没有选中文件".equals(tpDir.getText()) || "".equals(tpDir.getText())) {
                JOptionPane.showMessageDialog(this, "没有选中文件", "标题", JOptionPane.WARNING_MESSAGE);
            } else {
                System.out.println(System.getProperty("user.dir"));
                System.out.println("开始日期：" + startDp.getText());
                System.out.println("结束日期：" + endDp.getText());
                init();
                try {
                    //定义一个新的工作簿
                    Workbook workbook = new HSSFWorkbook();
                    /*
                     *创建一个sheet页
                     * 返回一个Sheet对象，我们要去接收他
                     */
                    Sheet sheet = workbook.createSheet("Sheet1");
                    /*
                     *创建行，参数是下标，从第0行开始
                     * 返回值类型是Row，需要接收
                     */
                    Row row = sheet.createRow(0);
                    /*
                     * 创建列，行加上列就是excel表格里面的单元格
                     * 返回值类型是Cell，需要接收
                     * 0代表第一列
                     */
                    Cell cell = row.createCell(0);
                    /*
                     * 给第一列第一个单元格赋值
                     * 值是林黛玉
                     */
                    cell.setCellValue("耗用统计表");
                    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 18);
                    sheet.addMergedRegion(region);
                    sheet.setColumnWidth(18, 20 * 256);

                    /*
                     *创建行，参数是下标，从第0行开始
                     * 返回值类型是Row，需要接收
                     */
                    Row row1 = sheet.createRow(1);
                    /*

                     */
                    row1.createCell(0).setCellValue("编号");
                    row1.createCell(1).setCellValue("碎石");
                    row1.createCell(2).setCellValue("碎石");
                    row1.createCell(3).setCellValue("黄砂");
                    row1.createCell(4).setCellValue("碎石");
                    row1.createCell(5).setCellValue("碎石");
                    row1.createCell(6).setCellValue("黄砂");
                    row1.createCell(7).setCellValue("水泥");
                    row1.createCell(8).setCellValue("水泥");
                    row1.createCell(9).setCellValue("粉煤灰");
                    row1.createCell(10).setCellValue("矿粉");
                    row1.createCell(11).setCellValue("彭润土");
                    row1.createCell(12).setCellValue("水");
                    row1.createCell(13).setCellValue("水");
                    row1.createCell(14).setCellValue("外加剂");
                    row1.createCell(15).setCellValue("外加剂");
                    row1.createCell(16).setCellValue("外加剂1");
                    row1.createCell(17).setCellValue("方量");
                    row1.createCell(18).setCellValue("时间");

                    Row row2 = sheet.createRow(2);
                    row2.createCell(0).setCellValue("品种规格");
                    row2.createCell(1).setCellValue("10-20");
                    row2.createCell(2).setCellValue("5-10");
                    row2.createCell(3).setCellValue("中砂");
                    row2.createCell(4).setCellValue("5-25");
                    row2.createCell(5).setCellValue("5-16");
                    row2.createCell(6).setCellValue("细砂");
                    row2.createCell(7).setCellValue("PO42.5");
                    row2.createCell(8).setCellValue("PO52.5");
                    row2.createCell(9).setCellValue("I级");
                    row2.createCell(10).setCellValue("S95");
                    row2.createCell(11).setCellValue("");
                    row2.createCell(12).setCellValue("自来水1");
                    row2.createCell(13).setCellValue("回收水");
                    row2.createCell(14).setCellValue("DS2-2");
                    row2.createCell(15).setCellValue("ACE8101");
                    row2.createCell(16).setCellValue("DS2-2");
                    row2.createCell(17).setCellValue("");
                    row2.createCell(18).setCellValue("");

                    String dir = tpDir.getText().replace("\\", "/");
                    List<HashMap<Integer, String>> data = getData(tPid.getText(), simpleDateFormat.parse(startDp.getText()),
                            simpleDateFormat.parse(endDp.getText()), dir);
                    HSSFCellStyle cellStyle = (HSSFCellStyle) workbook.createCellStyle();
                    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.0"));
                    //设置水平对齐的样式为居中对齐;
                    cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                    //设置垂直对齐的样式为居中对齐;
                    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    if (data.size() > 0) {
                        int count = 3;
                        for (HashMap<Integer, String> maps : data) {
                            Row rowTmp = sheet.createRow(count++);
                            Cell cell0 = rowTmp.createCell(0);cell0.setCellValue(maps.get(0));
                            Cell cell1 = rowTmp.createCell(1);if (maps.get(1) != null && maps.get(1).length() > 0){cell1.setCellValue(Double.parseDouble(maps.get(1)));} cell1.setCellStyle(cellStyle);
                            Cell cell2 = rowTmp.createCell(2);if (maps.get(2) != null && maps.get(2).length() > 0){cell2.setCellValue(Double.parseDouble(maps.get(2)));} cell2.setCellStyle(cellStyle);
                            Cell cell3 = rowTmp.createCell(3);if (maps.get(3) != null && maps.get(3).length() > 0){cell3.setCellValue(Double.parseDouble(maps.get(3)));} cell3.setCellStyle(cellStyle);
                            Cell cell4 = rowTmp.createCell(4);if (maps.get(4) != null && maps.get(4).length() > 0){cell4.setCellValue(Double.parseDouble(maps.get(4)));} cell4.setCellStyle(cellStyle);
                            Cell cell5 = rowTmp.createCell(5);if (maps.get(5) != null && maps.get(5).length() > 0){cell5.setCellValue(Double.parseDouble(maps.get(5)));} cell5.setCellStyle(cellStyle);
                            Cell cell6 = rowTmp.createCell(6);if (maps.get(6) != null && maps.get(6).length() > 0){cell6.setCellValue(Double.parseDouble(maps.get(6)));} cell6.setCellStyle(cellStyle);
                            Cell cell7 = rowTmp.createCell(7);if (maps.get(7) != null && maps.get(7).length() > 0){cell7.setCellValue(Double.parseDouble(maps.get(7)));} cell7.setCellStyle(cellStyle);
                            Cell cell8 = rowTmp.createCell(8);if (maps.get(8) != null && maps.get(8).length() > 0){cell8.setCellValue(Double.parseDouble(maps.get(8)));} cell8.setCellStyle(cellStyle);
                            Cell cell9 = rowTmp.createCell(9);if (maps.get(9) != null && maps.get(9).length() > 0){cell9.setCellValue(Double.parseDouble(maps.get(9)));} cell9.setCellStyle(cellStyle);
                            Cell cell10 = rowTmp.createCell(10);if (maps.get(10) != null && maps.get(10).length() > 0){cell10.setCellValue(Double.parseDouble(maps.get(10)));} cell10.setCellStyle(cellStyle);
                            Cell cell11 = rowTmp.createCell(11);if (maps.get(11) != null && maps.get(11).length() > 0){cell11.setCellValue(Double.parseDouble(maps.get(11)));} cell11.setCellStyle(cellStyle);
                            Cell cell12 = rowTmp.createCell(12);if (maps.get(12) != null && maps.get(12).length() > 0){cell12.setCellValue(Double.parseDouble(maps.get(12)));} cell12.setCellStyle(cellStyle);
                            Cell cell13 = rowTmp.createCell(13);if (maps.get(13) != null && maps.get(13).length() > 0){cell13.setCellValue(Double.parseDouble(maps.get(13)));} cell13.setCellStyle(cellStyle);
                            Cell cell14 = rowTmp.createCell(14);if (maps.get(14) != null && maps.get(14).length() > 0){cell14.setCellValue(Double.parseDouble(maps.get(14)));} cell14.setCellStyle(cellStyle);
                            Cell cell15 = rowTmp.createCell(15);if (maps.get(15) != null && maps.get(15).length() > 0){cell15.setCellValue(Double.parseDouble(maps.get(15)));} cell15.setCellStyle(cellStyle);
                            Cell cell16 = rowTmp.createCell(16);if (maps.get(16) != null && maps.get(16).length() > 0){cell16.setCellValue(Double.parseDouble(maps.get(16)));} cell16.setCellStyle(cellStyle);
                            Cell cell17 = rowTmp.createCell(17);if (maps.get(17) != null && maps.get(17).length() > 0){cell17.setCellValue(Double.parseDouble(maps.get(17)));} cell17.setCellStyle(cellStyle);
                            Cell cell18 = rowTmp.createCell(18);cell18.setCellValue(maps.get(18));

                        }
                    }
                    //使用文件输出流输出文件
                    dir = System.getProperty("user.dir") + "\\tmp.xls";
                    FileOutputStream fileOutputStream = new FileOutputStream(dir);
                    //workbook.write方法：把内容写到流里面去
                    workbook.write(fileOutputStream);
                    //关闭文件输出流
                    fileOutputStream.close();

                    Runtime.getRuntime().exec("cmd /c start " + dir);
                } catch (Exception exception) {
                    exception.printStackTrace();
                }
            }
        }
    }

    private void init() {
        maps.put("碎石10-20", 1);
        maps.put("碎石5-10", 2);
        maps.put("黄砂中砂", 3);
        maps.put("碎石5-25", 4);
        maps.put("碎石5-16", 5);
        maps.put("黄砂细砂", 6);
        maps.put("水泥PO42.5", 7);
        maps.put("水泥PO52.5", 8);
        maps.put("粉煤灰I级", 9);
        maps.put("矿粉S95", 10);
        maps.put("彭润土", 11);
        maps.put("水自来水1", 12);
        maps.put("水回收水", 13);
        maps.put("外加剂DS2-2", 14);
        maps.put("外加剂ACE8101", 15);
        maps.put("外加剂1DS2-2", 16);
        maps.put("方量", 17);
        maps.put("时间", 18);
    }

    private List<HashMap<Integer, String>> getData(String orderNum, Date startDate, Date endDate, String dir) throws ClassNotFoundException, SQLException {

        List<HashMap<Integer, String>> list = new ArrayList<HashMap<Integer, String>>();

        // url表示需要连接的数据源的位置，此时使用的是JDBC-ODBC桥的连接方式，url是"jdbc:odbc:数据源名"
        String tmp = dir.charAt(0) + "";
        String url = "jdbc:Access:///" + tmp.toLowerCase() + dir.substring(1);
        //url = "jdbc:Access:///d:/test/mix.mdb";


        Class.forName("com.hxtt.sql.access.AccessDriver");
        Connection conn = DriverManager.getConnection(url);
        Statement stat = conn.createStatement();
        String sql = "SELECT 小票编号,完成时间 FROM 小票";
        boolean flag = false;
        if (orderNum != null && orderNum.length() > 0) {
            sql += " where 小票.定单编号 = " + orderNum;
            flag = true;
        }
        if (startDate != null && endDate != null && !flag) {
            SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            sql += " where 小票.完成时间 Between \'" + simpleDateFormat1.format(startDate) + "\' and \'"
                    + simpleDateFormat1.format(endDate) + "\'";

        }
        sql += " order by 完成时间";
        ResultSet rs = stat.executeQuery(sql);


        Connection subConn = DriverManager.getConnection(url);
        Statement subStat = subConn.createStatement();


        //结果去重
        LinkedHashSet<String> set = new LinkedHashSet<String>();
        while (rs.next()) {
            set.add(rs.getString(1));
        }
        if (set.size() > 0) {
            for (String s : set) {
                HashMap<Integer, String> hashMap = new HashMap<Integer, String>();
                //获取 小票编号
                //System.out.println("number:" + s);

                Class.forName("com.hxtt.sql.access.AccessDriver");

                String subSql = "SELECT 用料.小票编号, 用料.原料名, 用料.品种规格, 用料.用量, 小票.本车方量, 小票.完成时间 FROM 用料 left join 小票 on 用料.小票编号 = 小票.小票编号 where  用料.小票编号 = "
                        + s;
                if (startDate != null && endDate != null) {
                    SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                    subSql += " and 小票.完成时间 Between \'" + simpleDateFormat1.format(startDate) + "\' and \'"
                            + simpleDateFormat1.format(endDate) + "\'";
                }
                ResultSet subRs = subStat.executeQuery(subSql);


                while (subRs.next()) {
                    Integer value = maps.get("方量");
                    Integer value1 = maps.get("时间");
                    Integer value2 = maps.get(subRs.getString(2).trim() + "" + subRs.getString(3).trim());
                    hashMap.put(0, s);
                    if (hashMap.get(value2) != null) {
                        String s1 = hashMap.get(value2);
                        double double1 = Double.parseDouble(subRs.getString(4).trim()) + Double.parseDouble(s1);
                        hashMap.put(value2, Double.toString(double1));
                    }else {
                        hashMap.put(value2, subRs.getString(4).trim());
                    }
                    if (hashMap.get(value) != null) {
                        String s1 = hashMap.get(value);
                        double double1 = Double.parseDouble(subRs.getString(5).trim()) + Double.parseDouble(s1);
                        hashMap.put(value, Double.toString(double1));
                    }else {
                        hashMap.put(value, subRs.getString(5).trim());
                    }
                    hashMap.put(value1, simpleDateFormat.format(subRs.getDate(6)));
                }
                if (hashMap.size() > 0) {
                    list.add(hashMap);
                }
            }
        }


        return list;
    }

    public static void main(String[] args) {
        new JavaGui();
    }
}
